from APP.config.log_conf import log
import asyncio,os
from fastapi import HTTPException
from openpyxl import load_workbook

'''
excel解析基础服务
1. excel文件验证模块
2.动态表头解析器
3.进度跟踪
'''

class ExcelBaseServices:

    #执行文件存在性、文件名白名单验证
    @staticmethod
    async def validate_file(filename:str) -> str:
        allowed_files = ['text.xlsx']

        current_dir = os.path.dirname(os.path.abspath(__file__))
        upload_dir = os.path.join(current_dir, '../upload')
        file_path = os.path.join(upload_dir, filename)
        # 文件存在性检查 [[9]]
        if not os.path.exists(file_path):
            raise HTTPException(404, detail=f"{filename}文件不存在")
        
        # 文件名白名单验证
        if filename not in allowed_files:
            raise HTTPException(400, detail=f"禁止的文件名:{filename}")
        
        return file_path
    
    #动态表头解析器
    @staticmethod
    async def dynamic_column_mapping(file_path:str, sheet_name:str) -> dict:

        wb = load_workbook(file_path, read_only=True)
        sheet = wb[sheet_name]

        #iter_rows(max_row=1) 读取第一行数据，遍历第一行的每个单元格，获取单元格的值，存入列表中
        headers = [cell.value for cell in next(sheet.iter_rows(max_row=1))]
        #将excel表指定sheet中的表头进行标准化，后面要从数据库动态提取,后续从excel提取的数据就按照这个列名进行匹配，不会因为excel表头的顺序变化而导致数据解析错误
        required_columns = ['姓名', '年龄', '部门']
        
        column_indices = {}
        for col in required_columns:
            try:
                column_indices[col] = headers.index(col)
            except ValueError:
                # 将 ValueError 转换为 HTTPException，返回给 response body
                raise HTTPException(status_code=400, detail=f"缺失必要列：{col}")
        
        return column_indices
    
    #解析指定excel、sheet表中的数据到数据库
    @staticmethod
    async def parse_excel(file_path:str, sheet_name:str, column_map:dict):
        wb = load_workbook(file_path, read_only=True)
        sheet = wb[sheet_name]
        #获取总行数，减去表头
        total_rows = sheet.max_row - 1

        data_rows = []
        current_row = 0
        for row in sheet.iter_rows(min_row=2):
            #将每行数据存入字典中
            row_data = {}
            #获取column_map字典中每个key
            for key in column_map:
                #获取column_map字典中每个key对应的值，也就是excel表中的列索引
                column_index = column_map[key]
                #获取excel表中指定列的值，存入字典中，key为column_map字典中每个key，value为excel表中指定列的值
                row_data[key] = row[column_index].value
            data_rows.append(row_data)
            current_row += 1

        return data_rows
    





        

ExcelBaseService:ExcelBaseServices = ExcelBaseServices()

